-- =============================================
-- Author:        Paul Bradshaw
-- Create date: Sept 19, 2006
-- Description:    Set DB Version string
-- =============================================
CREATE PROCEDURE [dbo].[asi_SetDatabaseVersion]
    @p1 nvarchar(20) = '' -- Specify the version string
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @version nvarchar(24)
    DECLARE @major nvarchar(10)
    DECLARE @minor nvarchar(10)
    DECLARE @patch nvarchar(10)
    DECLARE @build nvarchar(10)
    DECLARE @systemEntity uniqueidentifier
    DECLARE @type nvarchar(20)
    DECLARE @pos1 int
    DECLARE @pos2 int
    DECLARE @pos3 int
    DECLARE @pos4 int

    SET @version = UPPER(LTRIM(RTRIM(@p1)))

    SELECT @systemEntity = [SystemEntityKey] FROM [dbo].[SystemEntity] WHERE [SystemKeyword] = 'Organization'

    SELECT @major = [ParameterValue] FROM [dbo].[SystemConfig]
     WHERE [ParameterName] = 'System.Database.Version.Major'
       AND [OrganizationKey] = '00000000-0000-0000-0000-000000000000' AND [SystemEntityKey] = @systemEntity
    SELECT @minor = [ParameterValue] FROM [dbo].[SystemConfig]
     WHERE [ParameterName] = 'System.Database.Version.Minor'
       AND [OrganizationKey] = '00000000-0000-0000-0000-000000000000' AND [SystemEntityKey] = @systemEntity
    SELECT @patch = [ParameterValue] FROM [dbo].[SystemConfig]
     WHERE [ParameterName] = 'System.Database.Version.Patch'
       AND [OrganizationKey] = '00000000-0000-0000-0000-000000000000' AND [SystemEntityKey] = @systemEntity
    SELECT @build = [ParameterValue] FROM [dbo].[SystemConfig]
     WHERE [ParameterName] = 'System.Database.Version.Build'
       AND [OrganizationKey] = '00000000-0000-0000-0000-000000000000' AND [SystemEntityKey] = @systemEntity

    IF @version IS NOT NULL AND @version <> '' AND
       @major IS NOT NULL AND @minor IS NOT NULL AND @patch IS NOT NULL AND @build IS NOT NULL
    BEGIN
        IF @version <> 'INCBUILD'
        BEGIN
            SET @pos1 = CHARINDEX('.', @version, 1)
            IF (@pos1 > 0)
            BEGIN
                SET @major = SUBSTRING(@version, 1, @pos1 - 1)
                SET @pos2 = CHARINDEX('.', @version, @pos1 + 1)
                IF (@pos2 > 0)
                BEGIN
                    SET @minor = SUBSTRING(@version, @pos1 + 1, (@pos2 - @pos1) -1)
                    SET @pos3 = CHARINDEX('.', @version, @pos2 + 1)
                    IF (@pos3 > 0)
                    BEGIN
                        SET @patch = SUBSTRING(@version, @pos2 + 1, (@pos3 - @pos2) -1)
                        SET @pos4 = CHARINDEX('.', @version, @pos3 + 1)
                        IF (@pos4 > 0)
                        BEGIN
                            SET @build = SUBSTRING(@version, @pos3 + 1, (@pos4 - @pos3) -1)
                        END
                        ELSE
                        BEGIN
                            SET @build = SUBSTRING(@version, @pos3 + 1, LEN(@version))
                        END
                    END
                    ELSE
                    BEGIN
                        SET @patch = SUBSTRING(@version, @pos2 + 1, LEN(@version))
                    END
                END
                ELSE
                BEGIN
                    SET @minor = SUBSTRING(@version, @pos1 + 1, LEN(@version))
                END
            END
            ELSE
            BEGIN
                SET @major = @version
            END
        END
        ELSE IF @version = 'INCBUILD'
        BEGIN
            SET @build = @build + 1
        END

        UPDATE [SystemConfig] SET [ParameterValue] = @major
         WHERE [ParameterName] = 'System.Database.Version.Major'
           AND [OrganizationKey] = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity
        UPDATE [SystemConfig] SET [ParameterValue] = @minor
         WHERE [ParameterName] = 'System.Database.Version.Minor'
           AND [OrganizationKey] = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity
        UPDATE [SystemConfig] SET [ParameterValue] = @patch
         WHERE [ParameterName] = 'System.Database.Version.Patch'
           AND [OrganizationKey] = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity
        UPDATE [SystemConfig] SET [ParameterValue] = @build
         WHERE [ParameterName] = 'System.Database.Version.Build'
           AND [OrganizationKey] = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity

        UPDATE [dbo].[System_Params]
           SET [ShortValue] = [dbo].[fn_asi_DatabaseVersion]('')
         WHERE [ParameterName] = 'System_Control.Version'

    END

END

GO